var express = require('express');
var Msdb = require("../../../db/Msdb.js");
var router = express.Router();   //可使用 express.Router 类创建模块化、可挂载的路由句柄
const jwt = require('jsonwebtoken');
var Excel = require('exceljs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
const { getxl_util, getnowdate, saveczlog_util, res_util, checkbysql, addformfield, getnowdate_djh, setformfield,
   insert_table, update_table_wstr, update_table_byid, resutdata_to_formdata, resutdata_to_formdata_arr, save_qlczslog_util,
   usequery } = require('./server_util.js')

const log = require('../../../logger.js');
const db = require('../../../db/Redisdb.js');
var logger = require('../../../logger.js').logger;


//查询生产订单列表
router.post('/scdd_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_scdd order by id desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询生产订单列表通过id
router.post('/query_scdd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_scdd where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_scdd_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增生产订单
router.post('/add_scdd', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util()
   var insrtobj = await insert_table(formdata, "shipSRM_scdd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增生产订单', '新增', 'shipSRM_scdd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_scdd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增生产订单明细', '新增', 'shipSRM_scdd_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_scdd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "新增生产订单成功")
})

//更新生产订单
router.post('/update_scdd', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_scdd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改生产订单', '修改', 'shipSRM_scdd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_scdd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_scdd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_scdd_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_scdd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_scdd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新生产订单成功")
})

//  删除生产订单
router.post('/delete_scdd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_scdd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_scdd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除生产订单', '删除', 'shipSRM_scdd')
   res_util(res, {}, true, "删除成功")
})





//查询工单列表
router.post('/gd_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_gd order by id desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询工单列表通过id
router.post('/query_gd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_gd where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_gd_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增工单
router.post('/add_gd', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util()
   var insrtobj = await insert_table(formdata, "shipSRM_gd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增工单', '新增', 'shipSRM_gd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_gd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增工单明细', '新增', 'shipSRM_gd_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_gd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "新增工单成功")
})

//更新工单
router.post('/update_gd', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_gd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改工单', '修改', 'shipSRM_gd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gd_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_gd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_gd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新工单成功")
})

//  删除工单
router.post('/delete_gd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_gd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_gd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除工单', '删除', 'shipSRM_gd')
   res_util(res, {}, true, "删除成功")
})




//查询生产领料单列表
router.post('/sclld_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_sclld order by id desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询生产领料单列表通过id
router.post('/query_sclld_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_sclld where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_sclld_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增生产领料单
router.post('/add_sclld', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util()
   var insrtobj = await insert_table(formdata, "shipSRM_sclld")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增生产领料单', '新增', 'shipSRM_sclld')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_sclld_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增生产领料单明细', '新增', 'shipSRM_sclld_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_sclld_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "新增生产领料单成功")
})

//更新生产领料单
router.post('/update_sclld', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_sclld")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改生产领料单', '修改', 'shipSRM_sclld')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_sclld_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_sclld_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_sclld_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_sclld_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_sclld_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新生产领料单成功")
})

//  删除生产领料单
router.post('/delete_sclld', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_sclld where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_sclld_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除生产领料单', '删除', 'shipSRM_sclld')
   res_util(res, {}, true, "删除成功")
})






//查询生产入库单列表
router.post('/scrkd_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_scrkd order by id desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询生产入库单列表通过id
router.post('/query_scrkd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_scrkd where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_scrkd_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增生产入库单
router.post('/add_scrkd', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util()
   var insrtobj = await insert_table(formdata, "shipSRM_scrkd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增生产入库单', '新增', 'shipSRM_scrkd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_scrkd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增生产入库单明细', '新增', 'shipSRM_scrkd_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_scrkd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "新增生产入库单成功")
})

//更新生产入库单
router.post('/update_scrkd', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_scrkd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改生产入库单', '修改', 'shipSRM_scrkd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_scrkd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_scrkd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_scrkd_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_scrkd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_scrkd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新生产入库单成功")
})

//  删除生产入库单
router.post('/delete_scrkd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_scrkd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_scrkd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除生产入库单', '删除', 'shipSRM_scrkd')
   res_util(res, {}, true, "删除成功")
})



module.exports = router; 